package com.hamizhong.iflowers.db.dao.hibernate.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.hamizhong.iflowers.db.dao.base.MyBaseHibernateDao;
import com.hamizhong.iflowers.db.dao.hibernate.IGoodsDao;
import com.hamizhong.iflowers.db.entity.Goods;
import com.hamizhong.iflowers.web.vo.GoodsDBVO;
import com.hamizhong.iflowers.web.vo.GoodsVO;

public class GoodsDaoImpl extends MyBaseHibernateDao<Goods, Integer> implements IGoodsDao {

	public List<Goods> findAll(final Integer pageNum, final int pageSize) {
		List<Goods> list = null;
		try {
			List result = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					//sortId 值大的排前面
					String hql = "from Goods t ";
					hql += " order by t.sortId desc";
					Query query = session.createQuery(hql);
					query.setFirstResult((pageNum-1)*pageSize);
					query.setMaxResults(pageSize);
					return query.list();
				}
			});
			list = result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}

	public int findTotalGoodsCnt() {
		try {
			List totalCntList = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					String hql = "select count(*) from Goods";
					Query query = session.createQuery(hql);
					return query.list();
				}
			});
			return Integer.valueOf(totalCntList.get(0).toString()).intValue();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

	public List<Goods> findGoodsByBarginFlag(final Integer pageNum, final Integer pageSize) {
		List<Goods> list = null;
		try {
			List result = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					//sortId 值大的排前面
					String hql = "from Goods t ";
					hql += "where t.isBargin = '1' ";
					hql += " order by t.sortId desc";
					Query query = session.createQuery(hql);
					query.setFirstResult((pageNum-1)*pageSize);
					query.setMaxResults(pageSize);
					return query.list();
				}
			});
			list = result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}

	public List<Goods> finByType(final Integer pageNum, final Integer pageSize, final String varietyId, final String colorId, final String purposeId, final String festivalId, final String targetId,Integer flowerFloridId, final Integer flowerCountId) {
		List<Goods> list = null;
		Session session = null;
		try {
			String sql = "select g.* from goods as g where g.goods_id in (";
				sql += "select gfm.goods_id from goods_festival_map as gfm where gfm.festival_id = '"+festivalId+"'";
				sql += " union ";
				sql += "select gpm.goods_id from goods_purpose_map as gpm where gpm.purpose_id = '"+purposeId+"'";
				sql += " union ";
				sql += "select gtm.goods_id from goods_target_map as gtm where gtm.target_id = '"+targetId+"'";
				sql += " union ";
				sql += "select gprodm.goods_id from goods_product_map as gprodm where gprodm.product_id in (";
				sql += 		"select p.product_id from product p where p.variety_id = '"+varietyId+"' or p.color_id = '"+colorId+"'";
				sql += ")";
			sql += ")";
			sql += " union ";
			sql += "select g2.* from goods as g2 where g2.flower_count_id = '"+flowerCountId+"'";
			session = getSession();
			Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(GoodsDBVO.class));
			List<GoodsDBVO> listTemp = query.list();
			list = new ArrayList<Goods>();
			Goods goods = null;
			for (GoodsDBVO goodsDBVO : listTemp) {
				goods = new Goods();
				goods.setGoodsId(goodsDBVO.getGoods_id());
				goods.setGoodsName(goodsDBVO.getGoods_name());
				goods.setGoodsPictureS(goodsDBVO.getGoods_picture_s());
				goods.setGoodsPrice(goodsDBVO.getGoods_price());
				goods.setIsBargin(goodsDBVO.getIs_bargin());
				goods.setBarginPrice(goodsDBVO.getBargin_price());
				goods.setSortId(goodsDBVO.getSort_id());
				goods.setRemarks(goodsDBVO.getRemarks());
				list.add(goods);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if(session != null) {
				session.close();
			}
		}
		return list;
	}

	public List<Goods> finByCondition(final Integer pageNum, final Integer pageSize, final String searchCondition) {
		List<Goods> list = null;
		try {
			List result = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					//sortId 值大的排前面
					String hql = "from Goods t ";
					hql += "where t.goodsName like '%"+searchCondition+"%' ";
					hql += "or t.remarks like '%"+searchCondition+"%' ";
					hql += "or t.goodsPrice like '%"+searchCondition+"%' ";
					hql += "or t.barginPrice like '%"+searchCondition+"%' ";
					hql += " order by t.sortId desc";
					Query query = session.createQuery(hql);
					query.setFirstResult((pageNum-1)*pageSize);
					query.setMaxResults(pageSize);
					return query.list();
				}
			});
			list = result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}

	public List<Goods> findByIsBargin(final Integer pageNum, final int pageSize, final Integer isBargin) {
		List<Goods> list = null;
		try {
			List result = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					//sortId 值大的排前面
					String hql = "from Goods t ";
					hql += " where t.isBargin =:ib ";
					hql += " order by t.sortId desc";
					Query query = session.createQuery(hql);
					query.setInteger("ib", isBargin);
					query.setFirstResult((pageNum-1)*pageSize);
					query.setMaxResults(pageSize);
					return query.list();
				}
			});
			list = result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return list;
	}

	public int findTotalGoodsCntByIsBargin(final Integer isBargin) {
		try {
			List totalCntList = this.getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					String hql = "select count(*) from Goods t ";
					hql += " where t.isBargin =:ib ";
					Query query = session.createQuery(hql);
					query.setInteger("ib", isBargin);
					return query.list();
				}
			});
			return Integer.valueOf(totalCntList.get(0).toString()).intValue();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}


}
